<?php
class Shepherd_Model_Mapper_PostData extends Shepherd_Model_Mapper_Abstract
{ 
	protected $_dbName;

	public function __construct()
	{
		parent::__construct();
		$this->_dbName = $this->getCurrentShepherdSchema() . '.postdata';
	}
	
	/**
	 * Get Db table object
	 *
	 * @return Model_DbTable_PostData
	 */
	public function getDbTable($instanceId = null)
	{
		if (null === $this->_dbTable) {
			$this->setDbTable('Shepherd_Model_DbTable_PostData',$instanceId);
		}
		return $this->_dbTable;
	}
	
	/**
	 * Map row object data to the site object properties
	 *
	 * @param  Zend_Db_Table_Row $row
	 * @throws Exception
	 * @return Model_PostData mapped object
	 */
	public function mapData($row)
	{
		if (!$row instanceof Zend_Db_Table_Row) {
			throw new Exception('Invalid table row provided');
		}
		$postdata = new Shepherd_Model_PostData();
		$postdata->setDataId($row['dataid'])
				->setHardware($row['hardware'])
				->setCreatedDate($row['created_date'])
				->setSiteName($row['site_name'])
				->setTemperature1($row['temperature1'])
				->setBatteryVoltage($row['battery_voltage'])
				->setShelfLife($row['shelf_life'])
				->setDoSlLpd($row['do_sl_lpd'])
				->setEtPt100($row['et_pt100'])
				->setAirPressure($row['air_pressure'])
				->setShock($row['shock'])
				->setFreeFall($row['free_fall'])
				->setLightIntensity($row['light_intensity'])
				->setCo2($row['co2'])
				->setContact($row['contact'])
				->setUpdatedDate($row['updated_date'])
				->setRelativeHumidity($row['relative_humidity'])
				->setSend($row['send'])
				->setPosType($row['pos_type'])
				->setLatValue($row['lat_value'])
				->setLongValue($row['long_value'])
				->setError($row['error'])
				->setSpeed($row['speed'])
				->setHeading($row['heading']);
		return $postdata;
	}
	
	/**
	 * Save PostData object in database
	 *
	 * @param Model_PostData $postdata
	 *
	 * @return int The primary key of the row inserted or the number of rows updated
	 */
	public function save($postdata, $instanceId = null)
	{
		parent::save($postdata);
		$data = array(
				"dataid"            => $postdata->getDataId(),
				"hardware"          => $postdata->getHardware(),
				"created_date"      => $postdata->getCreatedDate(),
				"site_name"         => $postdata->getSiteName(),
				"temperature1"      => $postdata->getTemperature1(),
				"battery_voltage"   => $postdata->getBatteryVoltage(),
				"shelf_life"        => $postdata->getShelfLife(),
				"do_sl_lpd"         => $postdata->getDoSlLpd(),
				"et_pt100"          => $postdata->getEtPt100(),
				"air_pressure"      => $postdata->getAirPressure(),
				"shock"             => $postdata->getShock(),
				"free_fall"         => $postdata->getFreeFall(),
				"light_intensity"   => $postdata->getLightIntensity(),
				"co2"               => $postdata->getCo2(),
				"contact"           => $postdata->getContact(),
				"updated_date"      => $postdata->getUpdatedDate(),
				"relative_humidity" => $postdata->getRelativeHumidity(),
				"send"              => $postdata->getSend(),
				"pos_type"          => $postdata->getPosType(),
				"lat_value"         => $postdata->getLatValue(),
				"long_value"        => $postdata->getLongValue(),
				"error"             => $postdata->getError(),
				"speed"             => $postdata->getSpeed(),
				"heading"           => $postdata->getHeading()
		);
		try {
			if (NULL === ($id = $postdata->getDataId())) {
				unset($data['dataid']);
				$postdata->setDataId($this->getDbTable($instanceId)->insert($data));
				return $postdata->getDataId();
			} else {
				return $this->getDbTable($instanceId)->update($data, array('dataid = ?' => $id));
			}
		} catch (Exception $e) {
			Sss_Ultility_Common::logger()->log($e, Zend_Log::ERR);
		}
	}
	
	/**
	 * Fetch all PostData
	 *
	 * @return array List of Model_PostData
	 */
	public function fetchAll($instanceId = null)
	{
		$resultSet = $this->getDbTable($instanceId)->fetchAll();
		$entries   = array();
		foreach ($resultSet as $row) {
			$entries[] = $this->mapData($row);
		}
		return $entries;
	}
	
	/**
	 * Fetch a Site PostData by Identifier
	 *
	 * @param $id
	 *
	 * @return Model_PostData
	 */
	public function findById($id, $instanceId = null)
	{
		$select = $this->getDbTable($instanceId)->select()->where('dataid = ?', $id);
		$row = $this->getDbTable($instanceId)->fetchRow($select);
		$entry   = NULL;
		if (!empty($row)) {
			$entry = $this->mapData($row);
		}
		return $entry;
	}
	
	public function deleteRow($dataId,$instanceId = null)
	{
		return $this->getDbTable($instanceId)->delete(array('dataid = ?' => $dataId));
	}
	
	public function _setTableName($instanceId)
	{
		$this->_dbName =  SHEPHERD_SUFIX . Sss_Ultility_Common::formatString($instanceId,Sss_Ultility_Setting::FORMAT_NUMBER)  . '.postdata';
	}

	public function checkDataExist($siteDbName, $createdDate, $hardware, $instanceId)
	{
		$tableName = SHEPHERD_SUFIX . Sss_Ultility_Common::formatString($instanceId,Sss_Ultility_Setting::FORMAT_NUMBER) . '.postdata';
		if($instanceId == null)
			$tableName = $this->_dbName;
		$query = "
				SELECT 
					* 
				FROM 
					$tableName 
				WHERE 
					created_date = '$createdDate' 
					AND hardware=$hardware 
					AND site_name='$siteDbName'";
		$row = $this->fetchObject($query, true, $instanceId);
		return $row;
	}

	public function checkDuplicateData($siteDbName, $createdDate, $hardware, $colDbName, $value, $instanceId=null)
	{
		$tableName = SHEPHERD_SUFIX . Sss_Ultility_Common::formatString($instanceId,Sss_Ultility_Setting::FORMAT_NUMBER) . '.postdata';
		if($instanceId == null)
			$tableName = $this->_dbName;

		$query = "
				SELECT 
					* 
				FROM 
					$tableName 
				WHERE 
					created_date = '$createdDate' 
				AND hardware=$hardware 
				AND site_name='$siteDbName' 
				AND $colDbName<>'$value'";
		$row = $this->fetchObject($query, true, $instanceId);
		return $row;
	}

	public function checkDuplicatePosData($siteDbName,$arrayValue = array(), $instanceId=null)
	{
		$createdDate = $arrayValue['created_date'];
		$hardware = $arrayValue['hardware'];
		$posType = $arrayValue['pos_type'];
		$latValue = $arrayValue['lat_value'];
		$longValue = $arrayValue['long_value'];
		$errorValue = $arrayValue['error'];
		$speedValue = $arrayValue['speed'];
		$headingValue = $arrayValue['heading'];
		$tableName = SHEPHERD_SUFIX . Sss_Ultility_Common::formatString($instanceId,Sss_Ultility_Setting::FORMAT_NUMBER) . '.postdata';
		if($instanceId == null)
			$tableName = $this->_dbName;

		$query = "
				SELECT 
					* 
				FROM 
					$tableName 
				WHERE 
					created_date = '$createdDate' 
					AND hardware=$hardware 
					AND site_name='$siteDbName' 
					AND  (
							pos_type<>'$posType' 
							OR lat_value<>'$latValue' 
							OR long_value<>'$longValue' 
							OR error<>'$errorValue' 
							OR speed<>'$speedValue' 
							OR heading<>'$headingValue'
							)
				";
		$row = $this->fetchObject($query, true, $instanceId);
		return $row;
	}

	public function checkEsixtedColumns($columnName = '', $instanceId=null) 
	{
		$databaseName = $this->getCurrentShepherdSchema($instanceId);

		$query = "
					SHOW COLUMNS 
					FROM 
						$databaseName.`postdata` 
					LIKE '".$columnName."'";
		$res = $this->fetchObject($query, true, $instanceId);
		//write query in log file
		Sss_Ultility_Common::loggerPostData()->log("CHECK POSTDATA: ".$query."/".$res, Zend_Log::INFO);
		if($res == false) {
			$addCol = "
						ALTER TABLE 
							$databaseName.`postdata` 
						ADD COLUMN 
							".$columnName." VARCHAR (50)
						";
			$this->executeQuery($addCol, true, $instanceId);
		}
	}

	public function getDataInPeriodTime($hardwareId, $typeCode, $fromDateTime, $toDateTime, $unitCode) 
	{
		$query = "
				SELECT 
					AVG($typeCode) as `value` 
				FROM " . $this->_dbName;
		$where = array();
		$where[] = "FROM_UNIXTIME(created_date)" . ">='".$fromDateTime."'";
		$where[] = "FROM_UNIXTIME(created_date)" . "<='".$toDateTime."'";
		$where[] = 'hardware ='.$hardwareId;
		$where   = count($where) ? ' WHERE ' . implode(' AND ', $where) : '';	
		return array('value' => $this->fetchObject($query . $where)->value, 'unitcode'=>$unitCode);;
	}

	public function getColumn($valueType)
	{
		$valueTypeQuery = '
							SELECT 
								typecode 
							FROM 
								' . $this->_db_ovis_name . '.systemtypes 
							WHERE 
								systemtypeid = ' . $valueType;
		$systemType = $this->fetchObject($valueTypeQuery);
		if (is_null($systemType) || empty($systemType)) {
			return null;
		}
		return $systemType->typecode;
	}

	public function getDataByHardware($hardwareId, $valueType, $arrayData)
	{
		$fromValue = $arrayData['from_value'];
		$toValue = $arrayData['to_value'];
		$fromDateTime = $arrayData['from_datetime'];
		$toDateTime = $arrayData['to_datetime'];
		$unitCode = $arrayData['unitcode'];
		$isAvg = $arrayData['average_value'];

		$valueTypeQuery = '
							SELECT 
								typecode 
							FROM 
								' . $this->_db_ovis_name . '.systemtypes 
							WHERE 
								systemtypeid = ' . $valueType;
		$systemType = $this->fetchObject($valueTypeQuery,true);

		if (is_null($systemType) || empty($systemType)) {
			return null;
		}

		$column = $systemType->typecode;

		if ($isAvg) { 
			$avg = "CONCAT('[''',DATE_FORMAT(FROM_UNIXTIME(created_date), '%m/%d/%Y %H:%i:%S'),'''' ,',' ,ROUND(AVG(" . $systemType->typecode . "), 2), ']') AS `value`";
		} else { 
			$avg = "CONCAT('[''',DATE_FORMAT(FROM_UNIXTIME(created_date), '%m/%d/%Y %H:%i:%S') ,'''' ,',' ,ROUND(MIN(" . $systemType->typecode . "), 2), ']') AS `value`";
		}

		$where  = array();
		$where[] = 'hardware'.'='.$hardwareId;

		if($fromValue != $toValue)
		{
			$where[] = $column.'>='.$fromValue;
			$where[] = $column.'<='.$toValue;
		}

		if($fromDateTime!=null && $toDateTime!=null)
		{
			$where[] = "FROM_UNIXTIME(created_date)" . ">='".$fromDateTime."'";
			$where[] = "FROM_UNIXTIME(created_date-59)" . "<='".$toDateTime."'";
		}

		$where[] = " $column IS NOT NULL ";
		$select = '';
		$groupby = '';
		$dateValue = "FROM_UNIXTIME(created_date) AS `created_date`";
			$select = " SELECT 
							" . $dateValue . " , 
							CONCAT('[''',DATE_FORMAT(FROM_UNIXTIME(created_date), '%m/%d/%Y %H:%i:%S'),'''' ,',' ,
							ROUND(" . $column . ", 2), ']') AS `value` 
						FROM " . $this->_dbName;
			$groupby = '';
		$where   = count($where) ? 'WHERE ' . implode(' AND ', $where) : '';
		$results = $this->loadAll($select.' '.$where.' '.$groupby . ' ORDER BY created_date', false);
		return is_null($results) ? null : array('value' => $results, 'unitcode'=>(($unitCode)));
	}

	public function getHardwarePostDataFromToPostData($hardwareId,$start,$end,$typeCode = null)
	{
		$typeCodeNotNull = '';
		if( !empty($typeCode) ) {
			$typeCodeNotNull = ' AND '.$typeCode.' IS NOT NULL';
		}
		$query = "
				SELECT 
					hardware,created_date 
				FROM 
					" . $this->_dbName . " 
				WHERE 
					hardware = ".$hardwareId." 
					AND created_date 
						BETWEEN ".$start." AND ".$end.$typeCodeNotNull." 
				ORDER BY 
					created_date ";

		$rowAll = $this->loadAll($query,false);
		if(is_null($rowAll) || empty($rowAll)){
			return null;
		}
		return $rowAll;
	}

	public function getDataMaxMinByHardware($hardwareId, $valueType, $arrayData)
	{
		$fromValue = $arrayData['from_value'];
		$toValue = $arrayData['to_value'];
		$fromDateTime = $arrayData['from_datetime'];
		$toDateTime = $arrayData['to_datetime'];

		$isAvg = $arrayData['average_value'];

		$valueTypeQuery = '
							SELECT 
								typecode 
							FROM 
								' . $this->_db_ovis_name . '.systemtypes 
							WHERE 
								systemtypeid = ' . $valueType;
		$systemType = $this->fetchObject($valueTypeQuery);
	
		if (is_null($systemType) || empty($systemType)) {
			return null;
		}
	
		$column = $systemType->typecode;
	
		if ($isAvg) {
			$avg = "CONCAT('[''',DATE_FORMAT(FROM_UNIXTIME(created_date), '%m/%d/%Y %H:%i:%S'),'''' ,',' ,ROUND(AVG(" . $systemType->typecode . "), 2), ']') as `value`";
		} else {
			$avg = "CONCAT('[''',DATE_FORMAT(FROM_UNIXTIME(created_date), '%m/%d/%Y %H:%i:%S') ,'''' ,',' ,ROUND(MIN(" . $systemType->typecode . "), 2), ']') as `value`";
		}
	
		$where  = array();
		$where[] = 'hardware='.$hardwareId;
	
		if($fromValue != $toValue)
		{
			$where[] = $column.'>='.$fromValue;
			$where[] = $column.'<='.$toValue;
		}
	
		if($fromDateTime!=null && $toDateTime!=null)
		{
			$where[] = "FROM_UNIXTIME(created_date)" . ">='".$fromDateTime."'";
			$where[] = "FROM_UNIXTIME(created_date-59)" . "<='".$toDateTime."'";
		}
	
		$where[] = " $column IS NOT NULL";
		$select = '';
		$groupby = '';
		$select = "
					SELECT 
						MAX(" . $column . "*1) AS `valueMax`,
						MIN(" . $column . "*1) AS`valueMin` 
					FROM 
						" . $this->_dbName;
		$orderby = 'ORDER BY '.$column.' DESC';
		$where   = count($where) ? 'WHERE ' . implode(' AND ', $where) : '';

		return $this->fetchObject($select.' '.$where.' '.$groupby .' '.$orderby, false);
	}

	public function getIdByData($hardware = null,$createDate = null,$siteName = '', $instanceId = null)
	{
		$tableName = SHEPHERD_SUFIX . Sss_Ultility_Common::formatString($instanceId,Sss_Ultility_Setting::FORMAT_NUMBER) . '.postdata';
		if($instanceId == null)
			$tableName = $this->_dbName;
		$query = "
				SELECT 
					dataid 
				FROM 
					$tableName
				WHERE 
					hardware = ".$hardware." 
				AND 
					created_date = ".$createDate." 
				AND 
					site_name = '".$siteName."'";
		return $this->fetchObject($query,true, $instanceId);
	}
}
